﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SeparateProjects
{
    public partial class Pro_Admin_Name_Search : Form
    {
        SqlConnection coon = new SqlConnection(@"Data Source = DESKTOP-28VSNS5;Initial Catalog = Training_Project; 
                                                Uid = Lv; Pwd = 123456;");
        public Pro_Admin_Name_Search()
        {
            InitializeComponent();
        }
        #region   创建数据集
        /// <summary>
        /// 封装一个数据集
        /// </summary>
        private void Controll()
        {
            //设计listView为详细视图
            this.listView1.View = View.Details;
            //设计listView的列
            this.listView1.Columns.Add("学号", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("姓名", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("年龄", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("性别", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("年级", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("身份证", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("出生日期", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("家庭地址", 100, HorizontalAlignment.Center);
            this.listView1.Columns.Add("密码", 100, HorizontalAlignment.Center);
            //是否整行选中
            this.listView1.FullRowSelect = true;
            //是否出现多选
            this.listView1.MultiSelect = false;
            //是否出现网格线
            this.listView1.GridLines = true;
        }
        #endregion
        #region   点击查询信息
        /// <summary>
        /// 点击查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Search_Click(object sender, EventArgs e)
        {
            //清空所用行与列
            this.listView1.Clear();
            Controll();
            try
            {
                coon.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append(@"select  S.Sid,D.UserName,S.Stu_StuInfo_Name,SE.Sex_Name,S.Stu_StuInfo_Age,G.Grede_Name,
                            S.Stu_StuInfo_Card,S.Stu_StuInfo_Birthday,S.Stu_StuInfo_Address,D.Password
                            from Stu_StuInfo S
                            inner join Stu_Login D on(S.Sid = D.Sid)
                            inner join Sex Se on(Se.Stu_StuInfo_Sex = S.Stu_StuInfo_Sex)
                            inner join Grade G on(G.Stu_StuInfo_Grade = S.Stu_StuInfo_Grade)
                            where 1 = 1");

                var UserName = this.txt_UserName.Text.Trim();
                if (!string.IsNullOrWhiteSpace(UserName))
                {
                    sb.AppendFormat(" and Stu_StuInfo_Name like '%{0}%'", UserName);
                }
                SqlCommand comm = new SqlCommand(sb.ToString(), coon);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //ListView的行对象
                    ListViewItem obj = new ListViewItem();
                    //设置当前行的第一列数据
                    obj.Text = Convert.ToString(dr["UserName"]);
                    //设置其他列的数据
                    obj.SubItems.Add(Convert.ToString(dr["Stu_StuInfo_Name"]));
                    obj.SubItems.Add(Convert.ToString(dr["Stu_StuInfo_Age"]));
                    obj.SubItems.Add(Convert.ToString(dr["Sex_Name"]));
                    obj.SubItems.Add(Convert.ToString(dr["Grede_Name"]));
                    obj.SubItems.Add(Convert.ToString(dr["Stu_StuInfo_Card"]));
                    obj.SubItems.Add(Convert.ToString(dr["Stu_StuInfo_Birthday"]));
                    obj.SubItems.Add(Convert.ToString(dr["Stu_StuInfo_Address"]));
                    obj.SubItems.Add(Convert.ToString(dr["Password"]));
                    this.listView1.Items.Add(obj);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员" + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  修改个人信息
        /// <summary>
        /// 修改个人信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Modify_Personal_Information_Click(object sender, EventArgs e)
        {
            var UserName = Convert.ToInt32(this.listView1.SelectedItems[0].SubItems[0].Text);
            Pro_Admin_Modify_Information fs = new Pro_Admin_Modify_Information(UserName);
            fs.ShowDialog();
        }
        #endregion
        #region  删除用户信息
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Delete_User_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("确认删除该信息", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            if (dr.Equals(DialogResult.OK))
            {
                Number();
                try
                {
                    coon.Open();
                    var sql = string.Format(@"select count(*) from Score
                                              where Sid = '{0}'", this.lbl_Sid.Text.Trim());
                    var comm = new SqlCommand(sql, coon);
                    object obj = comm.ExecuteScalar();
                    int num = int.Parse(obj.ToString());
                    if (num > 0)
                    {
                        Achievement();
                    }
                    else
                    {
                        XinXi();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("服务器异常，请联系管理员   " + ex.Message);
                }
                finally
                {
                    coon.Close();
                }
            }
        }
        #endregion
        #region  添加用户成绩
        /// <summary>
        /// 添加用户成绩
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void increase_Score_Click(object sender, EventArgs e)
        {
            var Sid = Convert.ToInt32(this.listView1.SelectedItems[0].SubItems[0].Text);
            Pro_Admin_Increase_Score abc = new Pro_Admin_Increase_Score(Sid);
            abc.ShowDialog();
        }
        #endregion
        #region  获取编号
        public void Number()
        {
            var UserName = Convert.ToInt32(this.listView1.SelectedItems[0].SubItems[0].Text);
            try
            {
                coon.Open();
                var sql = string.Format(@"select S.Sid from Stu_StuInfo S
                                        inner join Stu_Login L on(L.Sid = S.Sid)
                                        where L.UserName = '{0}'", UserName);
                var comm = new SqlCommand(sql, coon);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    var Sid = Convert.ToString(dr["Sid"]);
                    this.lbl_Sid.Text = Sid;
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员   " + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  删除成绩及其信息
        public void Achievement()
        {
            var Sid = this.lbl_Sid.Text.Trim();
            try
            {
                var sql = string.Format(@"delete from Score
                                          where Sid = '{0}'", Sid);
                var sql1 = string.Format(@"delete from Stu_StuInfo 
                                          where Sid = '{0}'", Sid);
                var sql2 = string.Format(@"delete from Stu_Login
                                            where Stu_Login.Sid = '{0}'", Sid);
                var comm = new SqlCommand(sql, coon);
                var conm = new SqlCommand(sql1, coon);
                var comn = new SqlCommand(sql2, coon);
                int num = comm.ExecuteNonQuery();
                int number = conm.ExecuteNonQuery();
                var Number = comn.ExecuteNonQuery();
                if (num > 0 && number > 0 && Number > 0)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员   " + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
        #region  删除学生信息
        public void XinXi()
        {
            var Sid = this.lbl_Sid.Text.Trim();
            try
            {
                var sql = string.Format(@"delete from Stu_StuInfo 
                                          where Sid = '{0}'", Sid);
                var sql1 = string.Format(@"delete from Stu_Login
                                            where Stu_Login.Sid = '{0}'", Sid);
                var comm = new SqlCommand(sql, coon);
                var conm = new SqlCommand(sql1, coon);
                int num = comm.ExecuteNonQuery();
                int number = conm.ExecuteNonQuery();
                if (num > 0 && number > 0)
                {
                    MessageBox.Show("删除成功");
                }
                else
                {
                    MessageBox.Show("删除失败");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("服务器异常，请联系管理员        " + ex.Message);
            }
            finally
            {
                coon.Close();
            }
        }
        #endregion
    }
}
